/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yy.bugzillahelp.db;

import com.yy.bugzillahelp.test.SqliteTest;
import com.yy.bugzillahelp.tool.SQLTool;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author hualun-alan
 */
public class BugzillaHelpDB {

    private static final Logger logger = Logger.getLogger(BugzillaHelpDB.class.getName());
    private static String dbPath = System.getProperty("user.dir") + "\\db\\bugzilla_help.db";

    public static void drop() {
        Connection conn = null;
        try {
            conn = getConnection();

            conn.setAutoCommit(false);
            Statement stat = conn.createStatement();
            stat.executeUpdate(getServerInfoTableSql()[0]);
            stat.executeUpdate(getBugInfoTableSql()[0]);
            stat.executeUpdate(getBugStatusTableSql()[0]);
            stat.executeUpdate(getBugLongDescTableSql()[0]);
            stat.executeUpdate(getProductTableSql()[0]);
            stat.executeUpdate(getComponentTableSql()[0]);
            stat.executeUpdate(getVersionTableSql()[0]);
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(SqliteTest.class.getName()).log(Level.SEVERE, "Create table exception!", ex);
        } finally {
            SQLTool.closeConnection(conn);
        }
    }

    public static void create() {
        Connection conn = null;
        try {
            conn = getConnection();

            conn.setAutoCommit(false);
            Statement stat = conn.createStatement();
//            stat.executeUpdate(getServerInfoTableSql()[1]);
//            stat.executeUpdate(getBugInfoTableSql()[1]);
//            stat.executeUpdate(getBugStatusTableSql()[1]);
//            stat.executeUpdate(getBugLongDescTableSql()[1]);
            stat.executeUpdate(getProductTableSql()[1]);
            stat.executeUpdate(getComponentTableSql()[1]);
            stat.executeUpdate(getVersionTableSql()[1]);
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(SqliteTest.class.getName()).log(Level.SEVERE, "Create table exception!", ex);
        } finally {
            SQLTool.closeConnection(conn);
        }
    }

    private static String[] getServerInfoTableSql() {
        return new String[]{"drop table server_info",
            "create table server_info("
            + "id varchar(30) PRIMARY KEY,"
            + "server_name varchar(50) UNIQUE,"
            + "server_url varchar(200),"
            + "user_name varchar(50),"
            + "password varchar(50),"
            + "auto_flush varchar(3),"
            + "flush_interval long_int,"
            + "last_flush_time long_int"
            + ")"};
    }

    private static String[] getBugInfoTableSql() {
        return new String[]{"drop table bug_detail",
            "create table bug_detail("
            + "bug_id varchar(30) PRIMARY KEY,"
            + "create_time varchar(30),"
            + "short_desc varchar(200),"
            + "modify_time varchar(30),"
            + "product varchar(50),"
            + "component varchar(50),"
            + "version varchar(30),"
            + "bug_status varchar(50),"
            + "resolution varchar(50),"
            + "priority varchar(30),"
            + "bug_severity varchar(30),"
            + "reporter varchar(50),"
            + "assigned_to varchar(50),"
            + "server_id varchar(30)"
            + ")"};
    }

    private static String[] getBugStatusTableSql() {
        return new String[]{"drop table bug_status",
            "create table bug_status("
            + "bug_id varchar(30) PRIMARY KEY,"
            + "whole_status varchar(3),"
            + "create_time varchar(3),"
            + "short_desc varchar(3),"
            + "delta_time varchar(3),"
            + "product varchar(3),"
            + "component varchar(3),"
            + "version varchar(3),"
            + "bug_status varchar(3),"
            + "resolution varchar(3),"
            + "priority varchar(3),"
            + "bug_severity varchar(3),"
            + "reporter varchar(3),"
            + "assigned_to varchar(3),"
            + "long_desc varchar(3)"
            + ")"};
    }

    private static String[] getBugLongDescTableSql() {
        return new String[]{"drop table bug_comment",
            "create table bug_comment("
            + "comment_id varchar(30) PRIMARY KEY,"
            + "bug_id varchar(30),"
            + "comment_when varchar(50),"
            + "comment_who varchar(50),"
            + "comment_text text,"
            + "server_id varchar(30)"
            + ")"};
    }

    private static String[] getProductTableSql() {
        return new String[]{"drop table products",
            "create table products("
            + "product_id varchar(30) PRIMARY KEY,"
            + "name varchar(200),"
            + "desc text,"
            + "is_active varchar(3),"
            + "allwos_unconfirmed varchar(3),"
            + "server_id varchar(30)"
            + ")"};
    }

    private static String[] getComponentTableSql() {
        return new String[]{"drop table components",
            "create table components("
            + "component_id varchar(30) PRIMARY KEY,"
            + "name varchar(200),"
            + "desc text,"
            + "default_assigned_to varchar(200),"
            + "product_id varchar(30),"
            + "server_id varchar(30)"
            + ")"};
    }

    private static String[] getVersionTableSql() {
        return new String[]{"drop table versions",
            "create table versions("
            + "version_id varchar(30) PRIMARY KEY,"
            + "name varchar(200),"
            + "is_active varchar(3),"
            + "product_id varchar(30),"
            + "server_id varchar(30)"
            + ")"};
    }

    public static Connection getConnection() {
        try {
            Class.forName("org.sqlite.JDBC");
            logger.log(Level.INFO, "db path = {0}", dbPath);
            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
            return conn;
        } catch (SQLException ex) {
            Logger.getLogger(BugzillaHelpDB.class.getName()).log(Level.SEVERE, "Maybe DB Path is not right!", ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(BugzillaHelpDB.class.getName()).log(Level.SEVERE, "Error:You have no Sqlite JDBC Driver!!", ex);
        }

        return null;
    }

    public static void main(String[] args) {
//        drop();
        create();
    }
}
